package com.example.sec.db;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.Vector;

//import com.mysql.jdbc.PreparedStatement;
import com.example.sec.data.UserInfo;
import com.example.util.SecEncoder;
import com.example.util.SecEncrypt;

public class DbOperation {
	private static String url="jdbc:mysql://localhost/top10example"; 
	private static String user="testuser";
	private static String password="testuser";

	public static int executeUpdate(String strSQL){
		Connection conn = null;
		Statement stmt = null;
		try
		{
			conn = getConnection();
			stmt = conn.createStatement(); 
			
			int nCount = stmt.executeUpdate(strSQL);
			stmt.close();
			conn.close();
			return nCount;
		} catch (Exception e) {
			System.out.println("Fail to execute update:"+e.toString()); 
		} finally {
			try
			{
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			} 
		}
		
		return 0;
	}
	
	public static int modUser(UserInfo userInfo) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try
		{
			String query = "update user set name=?,passwd=?,email=?, firstname=?, lastname=?, phonenumber=?, address=? where id=?";
			conn = getConnection();
			
			String passwd = SecEncrypt.getSHA1Digest(userInfo.getPassword());

			stmt = conn.prepareStatement( query );
			stmt.setString( 1, userInfo.getUserName()); 
			stmt.setString( 2, passwd); 
			stmt.setString( 3, userInfo.getEmail()); 
			stmt.setString( 4, userInfo.getFirstName()); 
			stmt.setString( 5, userInfo.getLastName()); 
			stmt.setString( 6, userInfo.getPhoneNumber()); 
			stmt.setString( 7, SecEncoder.getUTF8Encoder(userInfo.getAddress())); 
			stmt.setLong(8, Integer.parseInt(userInfo.getId()));
			
			int res = stmt.executeUpdate();
			stmt.close();
			conn.close();

			return res;
		} catch (Exception e) {
			System.out.println("Fail to update user:"+e.toString()); 
		} finally {
			try
			{
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			} 
		}
		return -1;
	}
	
	public static int addUser(UserInfo userInfo) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try
		{
			String query = "insert into user(name, passwd, email, firstname, lastname, phonenumber, address)values(?,?,?,?,?,?,?) ";
			conn = getConnection();
			stmt = conn.prepareStatement( query );
			
			String passwd = SecEncrypt.getSHA1Digest(userInfo.getPassword());
			stmt.setString( 1, userInfo.getUserName()); 
			stmt.setString( 2, passwd); 
			stmt.setString( 3, userInfo.getEmail()); 
			stmt.setString( 4, userInfo.getFirstName()); 
			stmt.setString( 5, userInfo.getLastName()); 
			stmt.setString( 6, userInfo.getPhoneNumber()); 
			stmt.setString( 7, SecEncoder.getUTF8Encoder(userInfo.getAddress())); 
			
			int res = stmt.executeUpdate();
			stmt.close();
			conn.close();

			return res;
		} catch (Exception e) {
			System.out.println("Fail to get password:"+e.toString()); 
		} finally {
			try
			{
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			} 
		}
		return -1;
	}
	
	public static UserInfo getUserInfo(String userName){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		UserInfo userinfo = new UserInfo();
		try
		{
			String query = "select id,name, passwd, email, firstname, lastname, phonenumber, address from user where name=?";
			conn = getConnection();
			stmt = conn.prepareStatement( query );
			stmt.setString( 1, userName); 
			
			rs = stmt.executeQuery();
			while (rs.next()){
				userinfo.setId(rs.getString(1));
				userinfo.setUserName(rs.getString(2));
				userinfo.setPassword(rs.getString(3));
				userinfo.setEmail(rs.getString(4));
				userinfo.setFirstName(rs.getString(5));
				userinfo.setLastName(rs.getString(6));
				userinfo.setPhoneNumber(rs.getString(7));

				userinfo.setAddress(SecEncoder.getUTF8Encoder(rs.getString(8)));
			}

			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			System.out.println("Fail to get user info:"+e.toString()); 
		} finally {
			try
			{
				rs.close();
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			} 
		}

		return userinfo;
	}
	
	public static Vector getUserList(){
		Vector userList = new Vector();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try
		{
			String query = "select id,name, passwd, email, firstname, lastname, phonenumber, address from user where adminflag=0";
			conn = getConnection();
			stmt = conn.prepareStatement( query );
			// TODO:: Filter the html and js here
			rs = stmt.executeQuery();
			while (rs.next()){
				
				UserInfo userinfo = new UserInfo();
				userinfo.setId(rs.getString(1));
				userinfo.setUserName(rs.getString(2));
				userinfo.setPassword(rs.getString(3));
				userinfo.setEmail(rs.getString(4));
				userinfo.setFirstName(rs.getString(5));
				userinfo.setLastName(rs.getString(6));
				userinfo.setPhoneNumber(rs.getString(7));

				userinfo.setAddress(SecEncoder.getUTF8Encoder(rs.getString(8)));
				userList.add(userinfo);
			}

			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			System.out.println("Fail to get user list:"+e.toString()); 
		} finally {
			try
			{
				rs.close();
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			} 
		}

		return userList;
	}
	
	public static String getUserPassword(String userName){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String strPassword = null;
		try
		{
			String query = "select passwd from user where name= ? ";
			conn = getConnection();
			stmt = conn.prepareStatement( query );
			stmt.setString( 1, userName); 

			//stmt = conn.createStatement(); 
			
			rs = stmt.executeQuery();
			if (rs.next()){
				strPassword = rs.getString(1);
			}

			rs.close();
			stmt.close();
			conn.close();
			
			return strPassword;
		} catch (Exception e) {
			System.out.println("Fail to get password:"+e.toString()); 
		} finally {
			try
			{
				rs.close();
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			} 
		}

		return strPassword;
	}
	
	public static int getUserAdminFlag(String userName){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int adminFlag = 0;
		try
		{
			String query = "select adminflag from user where name= ? ";
			conn = getConnection();
			stmt = conn.prepareStatement( query );
			stmt.setString( 1, userName); 
			
			rs = stmt.executeQuery();
			if (rs.next()){
				adminFlag = rs.getInt(1);
			}

			rs.close();
			stmt.close();
			conn.close();
			
			return adminFlag;
		} catch (Exception e) {
			System.out.println("Fail to get adminFlag:"+e.toString()); 
		} finally {
			try
			{
				rs.close();
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			} 
		}

		return adminFlag;
	}
	
	public static boolean isUserExist(String userName){
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try
		{
			boolean bIsExist = false;
			conn = getConnection();
			
			String strSQL = "select * from user where name= ? ";

			stmt = conn.prepareStatement(strSQL); 
			stmt.setString(1, userName);
			rs = stmt.executeQuery();
			if (rs.next()){
				bIsExist = true;
			}

			rs.close();
			stmt.close();
			conn.close();
			
			return bIsExist;
		} catch (Exception e) {
			System.out.println("Fail to check user existence:"+e.toString()); 
		} finally {
			try
			{
				rs.close();
				stmt.close();
				conn.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			} 
		}

		return false;
	}

	private static Connection getConnection(){
		try{ 
			Class.forName("org.gjt.mm.mysql.Driver"); 
		}catch(Exception e){ 
			e.printStackTrace();
			return null;
		}
		Connection con = null;
		try {
			con = DriverManager.getConnection(url,user,password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		return con;
	}
}
